from flask import Blueprint, jsonify, request, json
import pymysql as sql

employee = Blueprint('employee', __name__)

connect = sql.Connect(
    host='bj-cdb-hjbj4oh0.sql.tencentcdb.com',
    port=60670,
    user='root',
    passwd='Yuan2000_',
    db='tencentmysql',
    charset='utf8'
)
cursor = connect.cursor()


@employee.route('/employeeInfo', methods=['GET'])
def employeeInfo():
    sql = "SELECT id,username, gender, phone,hire_date,ISACTIVE,imgset_dir FROM employee_info "
    cursor.execute(sql)

    attribute = ['id', 'username', 'gender', 'phone', 'hire_date', 'ISACTIVE','imgset_dir']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


@employee.route('/employeeInfoBySearch', methods=['POST'])
def employeeInfoBySearch():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username = json_data.get('username')
    sql = "SELECT id,username, gender, phone,hire_date,ISACTIVE FROM employee_info where username ='" + username + "'"
    cursor.execute(sql)

    attribute = ['id', 'username', 'gender', 'phone', 'hire_date', 'ISACTIVE']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


@employee.route('/newemployee', methods=['POST'])
def newemployee():
    print("new a newemployee")
    data = request.get_data()
    print(data)
    json_data = json.loads(data.decode("utf-8"))
    print(json_data)
    name=json_data.get('name')
    gender = json_data.get('gender')
    phone = json_data.get('phone')
    hire_date = json_data.get('hire_date')
    ISACTIVE = json_data.get('ISACTIVE')
    sql = "insert into employee_info  (`username`, `gender`, `phone`, `hire_date` ,`ISACTIVE`) values( '" + name + "', '" + gender + "','" + phone + "','" + hire_date + "','" + ISACTIVE + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(2)
    else:
        return jsonify(1)

@employee.route('/employeedelete', methods=['POST'])
def employeeDelete():
    data = request.get_data()
    print(data)
    word = data.decode("utf-8")
    print(word)
    sql = "Delete FROM employee_info WHERE id='" + str(word) + "'"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(2)
    else:
        return jsonify(1)

@employee.route('/editemp', methods=['POST'])
def editacc():
    print("new a accTypepany")
    data = request.get_data()
    print(data)
    json_data = json.loads(data.decode("utf-8"))
    print(json_data)
    id = json_data.get('id')
    name=json_data.get('name')
    gender = json_data.get('gender')
    phone = json_data.get('phone')
    hire_date = json_data.get('hire_date')
    ISACTIVE = json_data.get('ISACTIVE')

    sql = "update `employee_info` set `username`='" + name + "',`gender`='" + gender + "',`phone`='" + phone +"',`hire_date`='" + hire_date +"',`ISACTIVE`='" + ISACTIVE +"' where (`id`='" + id + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(2)
    else:
        return jsonify(1)


@employee.route('/employeeage', methods=['GET'])
def employeeage():
    sql = "SELECT id, age,hire_date,resign_date FROM employee_info "
    cursor.execute(sql)

    attribute = ['id', 'age','hire_date','resign_date']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)
